# Chapter 16. MySQL and mSQL System Variables

#### Contents:

MySQL System Variables
mSQL System Variables

Several variables can be used to customize the operation of MySQL and mSQL. Many of these are environment variables that are inherited from the user's shell, while others are set via command line options and configuration files.

## 16.1. MySQL System Variables

### 16.1.1. Environment Variables

The following variables are specific to MySQL programs. They may be defined in the current shell or as part of a shell script. To set a variable for the MySQL daemon (mysqld), define the variable in the safe_mysqld script that is used to start the daemon or define the variables in the MySQL configuration file (described later in this chapter).

MY_BASEDIR

MY_BASEDIR_VERSION

  The root directory containing the subdirectories `bin', `var' and `libexec' that contain the MySQL programs and data. A default value of this (usually compiled into MySQL as /usr/local) is used if this variable does not exist. This option affects only the mysqld program.

MYSQL_DEBUG

  The debugging level for the program. This option can be used with any MySQL program. The debugging library used by MySQL has many options. A list of all of the available options can be found at http://www.turbolift.com/mysql/appendixC.html. The most common set of options is `d:t:o,/tmp/debugfile`.

MYSQL_HOST

  The hostname used to connect to a remote MySQL database server. This option can be used with any of the MySQL client programs (mysql, mysqlshow, mysqladmin, etc.).

MYSQL_PWD

  The password used to connect to the MySQL database server. This option can be used with any of the MySQL client programs.

> #### WARNING

> Be careful where you put your passwords. A common use for environment variables is to set them within scripts. Of course, setting this particular variable in a script would make your password visible to anyone who can run the script. Even setting the variable manually on the command line exposes it to the superuser and any else who has the ability to examine the system memory.

MYSQL_TCP_PORT

  When used with a client program, this is the TCP port on a remote machine used to connect to the MySQL database server. When used with mysqld, this is the port used to listen for incoming connections.

MYSQL_UNIX_PORT

  When used with a client program, this is the Unix socket file used to connect to the MySQL database server. When used with mysqld, this is the name of the Unix socket file created that allows local connections.

In addition, the MySQL programs use the following environment variables that are routinely set as part of the Unix environment.

EDITOR
VISUAL

  The path of the default editor. The mysql program uses this program to edit SQL statements if a `\e` or `edit` command is encountered.

HOME

  The home directory of the current user.

LOGIN

LOGNAME

USER

  The username of the current user.

PATH

  The list of directories used to find programs.

POSIXLY_CORRECT

  If this variable is defined, no special processing is done on command line options. Otherwise, command line options are reordered so that extended options can be used. This variable can be used with any MySQL program.

TMP

TMPDIR

  The directory in which temporary files are kept. If this variable is not defined ``/tmp`' is used.

TZ

  The time zone of the local machine.

UMASK

  The umask used when creating new files.

### 16.1.2. Command line variables

These options are supplied via the `-O` or `-set-variable` command line option that is available in most MySQL programs.

back_log

  The number of TCP connections that can be queued at once. The default value is 5. This option is available for mysqld only.

connect_timeout

  The number of seconds the mysqld server waits for a connect packet before responding with Bad handshake.

decode-bits

  The number of bits used for generating certain internal tables. This should be a number between 4 and 9 (between 4 and 6 on a 16-bit operating system). The default value is 9. This option is available only for isamchk and should be used only if you understand the details of the `ISAM` table structure.

delayed_insert_limit

  Causes the `INSERT DELAYED` handler to check whether there are any `SELECT` statements pending after inserting delayed_insert_limit rows. If so, the handler allows the statements to execute before continuing.

delayed_insert_timeout

  How long an `INSERT DELAYED` thread should wait for `INSERT` statements to finish before terminating.

delayed_queue_size

  How big a queue (in rows) should be allocated for handling `INSERT` `DELAYED`. If the queue becomes full, any client that does an `INSERT` `DELAYED` must wait until there is room in the queue again.

dritebuffer

  The size of the buffer used to store outgoing data. The default value is 260KB. This option is only available for isamchk.

flush_time

  If set, all tables are closed then every flush_time seconds to free resources and synchronize changes to disk.

join_buffer

  The size of a buffer used when performing table joins. Increasing this can speed up performance for queries that join tables. The default value is 130 KB. This option is available only for mysqld.

key_buffer_size

  The size of a buffer allocated to store recently accessed keys. Increasing this can speed up performance for queries that involve the repeated use of the same keys. This option is available for isamchk (where the default value is 0.5 MB) and mysqld (default value of 1 MB).

long_query_time

  If set, the slow_queries counter is incremented each time a query takes longer than long_query_time seconds.

max_allowed_packet

  The maximum size of the buffer used to store incoming data. Each client connection has a separate buffer. The default value is 64 KB. This option is available only for mysqld.

max_connect_errors

  If set, the server blocks further connections from a remote host when the number of interrupted connections from that host exceeds max_connect_errors. You can unblock a host with the command `FLUSH HOSTS`.

max_connections

  The maximum number of simultaneous client connections. The default value is 90. This option can be used only with mysqld.

max_delayed_threads

  Start no more than this number of threads to handle `INSERT DELAYED`. If a client tries to use `INSERT DATA` to insert new data after this limit is reached, the request is handled as if the `DELAYED` attribute was not specified.

max_join_size

  The maximum size of a temporary table created by joining tables. The default value is 4 GB. This option can be used only with mysqld.

max_sort_length

  The maximum number of characters to examine when sorting a `BLOB` or `VARCHAR` field. The default value is 1KB.

max_tmp_tables

  (To be implemented later for Version 3.23.) Maximum number of temporary tables a client can keep open at the same time.

net_buffer_length

  The initial size of the buffer used to store incoming data. Each client connection has a separate buffer. The default value is 8KB. This option is available for mysql, mysqld, and mysqldump.

readbuffer

  The size of the buffer used to store data being read from files. The default value is 260KB. This option is available only for isamchk.

record_buffer

  The size of a buffer used to read data from the tables directly (that is, not using keys). Increasing this can speed up performance for queries that do not involve keys. The default value is 130KB. This option is available only for mysqld.

sortbuffer

  The size of the buffer used when sorting table data. The default value is 1MB. This option is available only for isamchk.

sort_buffer

  The size of the buffer used when performing sorts on retrieved data. Increasing this can speed up performance for queries that use `ORDER BY` or `GROUP BY` statements. The default value is 2MB. This option is available only for mysqld.

sort_key_blocks

  The number of blocks of keys used when sorting keys. This default value is 16. This option is only available for isamchk and should be used only if you understand the details of the `ISAM` table structure.

table_cache

  The maximum number of tables the database server can have open at once. The default value is 64. This option is only available for mysqld.

tmp_table_size

  The maximum size of temporary tables used by the database server. The default value is 1MB. This option is only available for mysqld.

thread_stack

  The size of the memory stack for each thread. The default value is 64KB. This option is only available for mysqld.

wait_timeout

  The number of seconds the server waits for activity on a connection before closing it.

### 16.1.3. The MySQL Configuration File

As of MySQL 3.22, you may specify both server and client options within a text configuration file. There is one format for this file which takes on different meaning depending on the location of the file. If the configuration file is stored in /etc/my.cnf, the options apply to all MySQL servers and clients on the machine. If it located in the data directory of a MySQL server (e.g., /usr/local/mysql/data/my.cnf) the options effect the operation of that MySQL server. Lastly, if the configuration file is named .my.cnf (note the initial period) and is located in the home directory of a user, it effects any clients run by that user.

The format of the file is similar to the one popularized by Windows initialization files. The file is broken up into stanzas, each with a group name enclosed in brackets. Underneath the group name is a list of options. Comments are indicated by a line beginning with `#` or `;`. Each group name is the name of a MySQL client or server program you wish the option to affect. The special group name `client` affects all MySQL client programs (everything except mysqld).

The options given in this file can be any long form command line option to any MySQL command (excluding the double-dash "`--`" option indentifier). Following is a sample server-wide my.cnf file.

>     [client]
>     port=9999
>     socket=/dev/mysql

>     [mysqld]
>     port=9999
>     socket=/dev/mysql
>     set-variable = join_buffer=1M

>     [mysql]
>     host=dbhost
>     unbuffered

Copyright © 2001 O'Reilly & Associates. All rights reserved.